package com.semac.server.database.classesbase;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


/**
 * A database wrapper for the web site selects. This class should not receive any complementing
 * methods
 * @author Martin Reus
 *
 */
public final class Database {	
	public static final String DRIVER = "org.postgresql.Driver";
	public static final String URL = "jdbc:postgresql://177.71.178.199:5432/sisweb_db";
	public static final String USER = "sisweb_user";
	public static final String PASSWORD = "siswebufrgs";
	
	private static Database databaseInstance = null;
	
	/**
	 * Do not instantiate this class. Instead, use the method {@link getDatabaseInstance()}
	 */
	private Database() {
		
	}
	
	/**
	 * Singleton instance of the Database adaptor.
	 * @see http://pt.wikipedia.org/wiki/Singleton for further reference on the singleton pattern
	 * @return An instance of this class
	 */
	public static Database getInstance(){
		if (databaseInstance == null){
			databaseInstance = new Database();
		}
		return databaseInstance;
	}
	
	/**
	 * Returns a connection handler to the database
	 * @return
	 * @throws SQLException
	 */
	private Connection getConnection() throws SQLException{
		Connection con = null;

		try {
			Class.forName(DRIVER).newInstance();
		} catch (Exception e) {
			throw new SQLException("Could not instantiate database class adaptor" );
		}
		
		try {
			con = DriverManager.getConnection(URL, USER, PASSWORD);
		} catch (SQLException e) {
			throw new SQLException("Could not connect to the database" );
		}

		return con;

	}
	
	
	/**
	 * To do a select, we provide an instance of ResultSetDataFetcher, which has a method
	 * that should be implemented in order to retrieve the data from the select. It is 
	 * done this way because it encapsulates the database connection and resultSet 
	 * connection, which has to be closed after the data is retrieved.
	 * 
	 * @param dataFetcherInstance an instance of ResultSetDataFetcher<TYPE>
	 * @throws SQLException
	 */
	public void doSelect(AbstractDataFetcher<?> dataFetcherInstance) throws SQLException{
		String selectionString = dataFetcherInstance.getSelectionString();
		Connection connection = this.getConnection();
		Statement stmt = null;
		try{
			stmt = connection.createStatement();
			ResultSet resultSet = stmt.executeQuery(selectionString);
			try{
				dataFetcherInstance.fetchDataFromResultSet(resultSet);
			}finally{
				resultSet.close();
			}
		}finally{
			if (stmt != null){
				stmt.close();
			}
			connection.close();
		}			
	}
	
	
	public void delete(String table, String where) throws SQLException{
		Connection con = this.getConnection();
		PreparedStatement stmt = con.prepareStatement("delete from " + table + " where " + where);

		stmt.executeUpdate();
		stmt.close();
		con.close();
	}
	
	
	
	public void update(String table, String set, String where) throws SQLException{
		Connection con = this.getConnection();
		PreparedStatement stmt = con.prepareStatement("update " + table + " set " + set + " where " + where);

		stmt.executeUpdate();
		stmt.close();
		con.close();
	}
	
	
	public int insert(String table, String fields, String  values) throws SQLException{
		int lastInsertedId = 0;
		Connection con = this.getConnection();
		PreparedStatement stmt = con.prepareStatement("insert into " 
													  + table + "(" + fields + ")" + 
													  " values " + "(" + values + ")",
									                  Statement.RETURN_GENERATED_KEYS);

		stmt.executeUpdate();
		ResultSet rs = stmt.getGeneratedKeys();
		while (rs.next())
			lastInsertedId = rs.getInt(1);
		rs.close();
		
		stmt.close();
		con.close();
		
		return lastInsertedId;
	}
	
	
}
